import pandas as pd
from sqlalchemy import create_engine
from app_config import get_engine_ts
from app_config import get_pro


def found_amount():
    # 构建查询语句，使用参数化查询
    query = """
           SELECT DISTINCT ts_code, name, fund_type, invest_type, market, benchmark 
           FROM `fund_basic_e` 
           WHERE benchmark in ('沪深300指数','沪深300指数收益率')
           """
    # 创建数据库引擎
    hs300_fund_e = pd.read_sql_query(query, get_engine_ts())

    # 将结果保存到 Excel
    hs300_fund_e.to_excel("fund_basic_e.xlsx", index=False)

    ts_code = ','.join(hs300_fund_e['ts_code'].unique())

    share = get_pro().fund_share(ts_code=ts_code, trade_date="20241202")

    fund_nav = get_pro().fund_nav(ts_code=ts_code, nav_date="20241202")
    fund_nav.to_excel("fund_nav.xlsx", index=False)

    share.to_excel("share.xlsx")

    hs300_fund_e = pd.merge(hs300_fund_e, share, on="ts_code", how="left")
    hs300_fund_e = pd.merge(hs300_fund_e,fund_nav, on="ts_code", how="left")
    hs300_fund_e['total_amount'] = hs300_fund_e['unit_nav'] * hs300_fund_e['fd_share']
    hs300_fund_e.to_excel("hs300_fund_e.xlsx")

    amount__sum = hs300_fund_e['total_amount'].sum()
    print(amount__sum / 10000)


if __name__ == '__main__':
    found_amount()

# 沪深300指数
# 沪深300指数收益率
